S3に格納したNetwork FirewallのログをAthenaで抽出してみた

hayashi.masaya

2022.05.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

こんにちは。大阪オフィスの林です。

S3に格納したNetwork Firewallのアラートログ、フローログをAthenaを使って抽出してみる機会がありましたので内容をまとめておきたいと思います。

やってみた

ログが格納されているバケットと、Athenaのクエリ保存用のバケットを準備します。

クエリ結果の格納先設定がまだの場合はAthenaのダッシュボード内「設定」から指定しておきます。

まずは任意の名前でデータベースを作成します。検証ではtestとします。

CREATE DATABASE test

サンプルに倣ってログを抽出してみる

下記サンプルに倣ってテーブルを作成してみます。

S3バケット名は環境に合わせて変更してください。

CREATE EXTERNAL TABLE anf_logs(
  firewall_name string,
  availability_zone string,
  event_timestamp bigint,
  event struct<
    timestamp:string,
    flow_id:bigint,
    event_type:string,
    src_ip:string,
    src_port:int,
    dest_ip:string,
    dest_port:int,
    proto:string,
    app_proto:string,
    netflow:struct<
      pkts:int,
      bytes:int,
      start:string,
      finish:string,
      age:int,
      min_ttl:int,
      max_ttl:int
    >
  >
)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'paths'='availability_zone,event,event_timestamp,firewall_name'
)
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-nfw-123456789012-1/AWSLogs/'

続いてもサンプル通りにデータを抽出してみます。

SELECT COUNT(*) AS count,
       event.src_ip,
       event.src_port,
       event.dest_ip,
       event.dest_port
FROM anf_logs
GROUP BY event.src_ip,
         event.src_port,
         event.dest_ip,
         event.dest_port
ORDER BY COUNT DESC
LIMIT 100

一応データが抽出できましたが、サンプルSQLクエリのままではどれがBlockされたログに該当するのかなど確認ができませんでした。

Blockのログを確認する

サンプルのテーブルではBlockされたログの確認ができませんでしたので、Blockされたログが確認できるようにテーブルを作っていきたいと思います。
下記のSQLクエリを実行しテーブルを作成するのですが、前述したSQLクエリと異なる点としてactionとsniのデータをテーブルに含めています。
Blockされたという情報だけを見極めるのであればactionだけでいいのですが、丁度ステートフルルールのDomain listアクション周りの検証をしていたので、ついでにどこ宛ての通信をBlockしたかも見るためsniの情報も含めています。

CREATE EXTERNAL TABLE anf_logs(
  firewall_name string,
  availability_zone string,
  event_timestamp bigint,
  event struct<
    timestamp:string,
    flow_id:bigint,
    event_type:string,
    src_ip:string,
    src_port:int,
    dest_ip:string,
    dest_port:int,
    proto:string,
    alert:struct<
               action:string
        >,
    tls:struct<
               sni:string
        >,
    app_proto:string,
    netflow:struct<
      pkts:int,
      bytes:int,
      start:string,
      finish:string,
      age:int,
      min_ttl:int,
      max_ttl:int
    >
  >
)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'paths'='availability_zone,event,event_timestamp,firewall_name'
)
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-nfw-123456789012-1/AWSLogs/'

Blockされたという情報とSNIの情報を含めてログを抽出してみます。（timestampも含めてます）

SELECT COUNT(*) AS count,
       event.timestamp,
       event.src_ip,
       event.src_port,
       event.dest_ip,
       event.dest_port,
       event.alert.action,
       event.tls.sni
FROM anf_logs
GROUP BY event.timestamp,
         event.src_ip,
         event.src_port,
         event.dest_ip,
         event.dest_port,
         event.alert.action,
         event.tls.sni
ORDER BY event.alert.action DESC
LIMIT 30

actionとsniのデータも無事に抽出できました。

備忘

元のデータ構造が分からないとテーブルを作るときやデータ抽出する際に困るので備忘で残しておきたいと思います。

アラートログのデータ構造

{
    "firewall_name": "test-nfw",
    "availability_zone": "ap-northeast-3a",
    "event_timestamp": "1653969127",
    "event": {
        "timestamp": "2022-05-31T03:52:07.712004+0000",
        "flow_id": 2208916996334463,
        "event_type": "alert",
        "src_ip": "10.202.3.73",
        "src_port": 56290,
        "dest_ip": "54.150.73.13",
        "dest_port": 443,
        "proto": "TCP",
        "tx_id": 0,
        "alert": {
            "action": "blocked",
            "signature_id": 3,
            "rev": 1,
            "signature": "matching TLS denylisted FQDNs",
            "category": "",
            "severity": 1
        },
        "tls": {
            "sni": "dev.classmethod.jp",
            "version": "UNDETERMINED",
            "ja3": {
                "hash": "a64c29923906f2a1be278be1da0714fa",
                "string": "771,49200-49196-49192-49188-49172-49162-165-163-161-159-107-106-105-104-57-56-55-54-136-135-134-133-49202-49198-49194-49190-49167-49157-157-61-53-132-49199-49195-49191-49187-49171-49161-164-162-160-158-103-64-63-62-51-50-49-48-154-153-152-151-69-68-67-66-49201-49197-49193-49189-49166-49156-156-60-47-150-65-49170-49160-22-19-16-13-49165-49155-10-7-255,0-11-10-13-15-13172-16-21,23-25-24-22,0-1-2"
            },
            "ja3s": {}
        },
        "app_proto": "tls"
    }
}

フローログのデータ構造

{
    "firewall_name": "test-nfw",
    "availability_zone": "ap-northeast-3a",
    "event_timestamp": "1653980412",
    "event": {
        "timestamp": "2022-05-31T07:00:12.242389+0000",
        "flow_id": 132734833004119,
        "event_type": "netflow",
        "src_ip": "10.202.3.73",
        "src_port": 38708,
        "dest_ip": "13.248.8.124",
        "dest_port": 443,
        "proto": "TCP",
        "app_proto": "tls",
        "netflow": {
            "pkts": 26,
            "bytes": 5528,
            "start": "2022-05-31T06:58:26.003671+0000",
            "end": "2022-05-31T06:58:46.041253+0000",
            "age": 20,
            "min_ttl": 254,
            "max_ttl": 254
        },
        "tcp": {
            "tcp_flags": "1f",
            "syn": true,
            "fin": true,
            "rst": true,
            "psh": true,
            "ack": true
        }
    }
}